package com.ifinc.common.db;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BaseJdbcDao {
    Logger log = LoggerFactory.getLogger(getClass());
    /**
     * 执行新增和修改的数据库操作
     *
     * @param sql    sql语句
     * @param params 参数
     * @return
     */
    public int executeSql(String sql, Object[] params) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = ConnectionFactory.getInstance().getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //log.debug("executeSql sql = " + sql);
            //log.debug("params = " + params);
            if (params != null) {
                //设置sql语句参数
                for (int i = 0; i < params.length; i++) {
                    //log.debug("params[i] = " + params[i]);
                    if (params[i] != null) {
                        if (params[i] instanceof java.util.Date) {
                            preparedStatement.setTimestamp(i + 1, new Timestamp(((java.util.Date) params[i]).getTime()));
                        } else {
                            preparedStatement.setObject(i + 1, params[i]);
                        }
                    } else {
                        preparedStatement.setString(i + 1, "");
                    }
                }
            }
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            log.error(e.getMessage() + "code = " + e.getErrorCode());
            throw new RuntimeException(e.getMessage() + "code = " + e.getErrorCode());
        } finally {
            ConnectionFactory.getInstance().closeConnection(connection, preparedStatement, resultSet);
        }

    }

    public int executeStatSql(String sql){
        Connection connection = null;
        Statement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = ConnectionFactory.getInstance().getConnection();
            preparedStatement = connection.createStatement();
            //log.debug("executeSql sql = " + sql);
            //log.debug("params = " + params);

            return preparedStatement.executeUpdate(sql);
        } catch (SQLException e) {
            log.error(e.getMessage() + "code = " + e.getErrorCode());
            throw new RuntimeException(e.getMessage() + "code = " + e.getErrorCode());
        } finally {
            ConnectionFactory.getInstance().closeConnection(connection, preparedStatement, resultSet);
        }
    }

    public int[] executeBatchSql(String sql, Object[][] paramsArr) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = ConnectionFactory.getInstance().getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //log.debug("executeSql sql = " + sql);

            if (paramsArr != null) {
                for (int s = 0; s < paramsArr.length; s++) {
                    Object[] params = paramsArr[s];
                    if (params != null) {
                        //设置sql语句参数
                        for (int i = 0; i < params.length; i++) {
                            //log.debug("params[i] = " + params[i]);
                            if (params[i] != null) {
                                if (params[i] instanceof java.util.Date) {
                                    preparedStatement.setTimestamp(i + 1, new Timestamp(((java.util.Date) params[i]).getTime()));
                                } else {
                                    preparedStatement.setObject(i + 1, params[i]);
                                }
                            } else {
                                preparedStatement.setString(i + 1, "");
                            }
                        }
                        preparedStatement.addBatch();
                    }
                }
            }
            return preparedStatement.executeBatch();
        } catch (SQLException e) {
            log.error(e.getMessage() + "code = " + e.getErrorCode());
            throw new RuntimeException(e.getMessage() + "code = " + e.getErrorCode());
        } finally {
            ConnectionFactory.getInstance().closeConnection(connection, preparedStatement, resultSet);
        }
    }

    /*
    * 执行sql操作，把sql和params结合成一个sql语句
    * 执行sql查询的结果集交给sqlExecute这个接口函数处理，处理后封装的对象放到List里
    * */
    public List queryList(String sql, Object[] params, ResultParser sqlExecute) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = ConnectionFactory.getInstance().getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //log.debug("queryList sql = " + sql);
            //log.debug("params = " + params);

            //设置sql语句参数
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    //log.debug("params[i] = " + params[i]);
                    preparedStatement.setObject(i + 1, params[i]);
                }
            }
            resultSet = preparedStatement.executeQuery();

            List result = new ArrayList();
            while (resultSet.next()) {
                Object obj = sqlExecute.parseObject(resultSet);
                result.add(obj);
            }
            return result;
        } catch (SQLException e) {
            log.error(e.getMessage() + "code = " + e.getErrorCode());
            throw new RuntimeException(e.getMessage() + "code = " + e.getErrorCode());
        } finally {
            ConnectionFactory.getInstance().closeConnection(connection, preparedStatement, resultSet);
        }
    }

    /**
     * 查询序列的方法
     *
     * @param name
     * @return
     */
    public Long getSeqValue(String name) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = ConnectionFactory.getInstance().getConnection();
            preparedStatement = connection.prepareStatement("select " + name + ".NEXTVAL as value from dual");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                return resultSet.getLong(1);
            }
            throw new RuntimeException("该序列不存在");
        } catch (SQLException e) {
            log.error(e.getMessage() + "code = " + e.getErrorCode());
            throw new RuntimeException(e.getMessage() + "code = " + e.getErrorCode());
        } finally {
            ConnectionFactory.getInstance().closeConnection(connection, preparedStatement, resultSet);
        }
    }
}
